/**
 * @author Mie Plougstrup, Anders Abildgaard, Bo Stokholm 
 * @Version 16-12-2014
 */

package dbLayer;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import modelLayer.Driver;
/**
 * @author Bo Nielsen
 * 
 * This class is for controlling the connection to the
 * database that contains all the drivers.
 * The class handle the associations that a driver 
 * might have.
 * */
public class DbDriver {
	private Connection con;
	private DbBusinessPartner dbbp;

	public DbDriver(){
		dbbp = new DbBusinessPartner();
		con = DbConnection.getInstance().getDBcon();
	}//Constructor

	/**
	 * Finds a driver by its driver id.
	 * 
	 * @param id
	 * @return Driver
	 */
	public Driver retrieveById(int id){
		String wClause = " driverId = " + id;
		return singleWhere(wClause);
	}
	
	/**
	 * Method for retrieving unused drivers.
	 * 
	 * @return A list of unused drivers.
	 */
	public ArrayList<Driver> retrieveUnusedDrivers(int dispListId) {

		return multiWhere(" driver.driverId != 0 and not exists (select * from disposition, dispositionList where disposition.driverId = driver.driverId and disposition.dispListId = dispositionList.dispListId and dispositionList.dispListId = " + dispListId + ")");
	}
	
	/**
	 * Finds a driver by its phonenumber.
	 * 
	 * @param String attValue
	 * @return Driver
	 */
	public Driver retrieveByPhone(String attValue){
		String wClause = " phoneNo like '%" + attValue + "%'";
		return singleWhere(wClause);
	}
	/**
	 * Finds the driver by first name
	 * 
	 * @param String attributeValue
	 * @return Driver
	 */
	public Driver retrieveByFName(String attValue){
		String wClause = " fName like '%" + attValue + "%'";
		return singleWhere(wClause);
	}
	/**
	 * Update a driver object.
	 * 
	 * @param Driver driver
	 * @return int rc
	 */

	/**
	 * Returns all the drivers in the database.
	 * 
	 * @return Arraylist<Driver>
	 */
	public ArrayList<Driver> retrieveAllDrivers(){
		return multiWhere("");
	}
	public int update(Driver driver){
		Driver driObj = driver;
		int rc = -1;

		String query = "UPDATE driver SET "+ 
				" fName = '" + driObj.getfName() + "', '" +
				" lName = '" + driObj.getlName() + "', '" +
				" phoneNo = '" + driObj.getPhoneNo() + 
				"' WHERE driverId = "+ driObj.getDriverId()+"";

		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);

			stmt.close();
		}catch(Exception e){
		}
		return (rc);
	}
	
	/**
	 * Insert driver into the database.
	 * 
	 * @param Driver driver
	 * @return int rc
	 * @throws Exception
	 */
	public int insert(Driver driver) throws Exception{
		//Get max driver id
		int nextId = DbMaxId.getMaxID("driverId", "Driver");
		nextId = nextId + 1;
		PreparedStatement pstmt = null;

		int rc = -1;
		//put supplierId in at last when dbscript is clear
		String query = "INSERT INTO Driver(driverId, fName, lName, phoneNo, supplierId) VALUES(?,?,?,?,?) ";

		try{
			driver.setDriverId(nextId);//Set driver id
			pstmt = con.prepareStatement(query);
			pstmt.setInt(1, driver.getDriverId());
			pstmt.setString(2, driver.getfName());
			pstmt.setString(3, driver.getlName());
			pstmt.setString(4, driver.getPhoneNo());
			pstmt.setInt(5, driver.getSupplier().getPartnerId());
			pstmt.setQueryTimeout(5);
			rc = pstmt.executeUpdate();
			pstmt.close();
		}catch(SQLException ex){
			System.out.println("Driver not created.");//Delete after tests
			throw new Exception("Driver not inserted.");
		}
		return (rc);
	}
	
	/**
	 * Delete a driver from the database.
	 * 
	 * @param Driver driver
	 * @return int rc
	 */
	public int delete(Driver driver){
		int rc = -1;

		String query = "DELETE FROM driver WHERE driverId = " + driver.getDriverId()+" ";
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}catch(Exception e){
			System.out.println("Didn't delete Driver.");
		}
		return (rc);
	}

	/**
	 * Executes a query statement and creates a single driver object
	 * that it returns.
	 * 
	 * @param String wClause
	 * @return Driver driver
	 */
	private Driver singleWhere(String wClause){
		ResultSet results;
		Driver driObj = new Driver();

		String query = buildQuery(wClause);

		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);

			if(results.next()){
				driObj = buildDriver(results);
				stmt.close();
			}
		}catch(Exception e){
			System.out.println(e);//Remove after test
		}
		return driObj;
	}

	/**
	 * Executes a query statement and then create a list of all the drivers
	 * that it returns.
	 * 
	 * @param String wClause
	 * @return Arraylist<Driver>
	 */
	private ArrayList<Driver> multiWhere(String wClause){
		ResultSet results;
		ArrayList<Driver> list = new ArrayList<Driver>();

		//Build query
		String query = buildQuery(wClause);

		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);

			//Create objects and add them to array list
			while(results.next()){
				Driver driObj = new Driver();
				driObj = buildDriver(results);
				list.add(driObj);
			}//End while

			//Close connection
			stmt.close();
		}catch(Exception e){
			//Remove after test
			System.out.println(e);
		}
		return list;
	}
	/**
	 * Builds SQL query that will be returned in a String.
	 * @param String wClause
	 * @return String query
	 */
	private String buildQuery(String wClause){
		String query = "SELECT driverId, fName, lName, phoneNo, supplierId FROM driver";
		//String query = "SELECT * FROM driver";
		if(wClause.length() > 0)
			query=query+" WHERE"+ wClause;

		return query;
	}
	/**
	 * Builds a driver object out of the resultset.
	 * @param ResultSet results
	 * @return Driver
	 */
	private Driver buildDriver(ResultSet results){
		Driver driObj = new Driver();
		try{
			driObj.setDriverId(results.getInt("driverId"));
			driObj.setfName(results.getString("fName"));
			driObj.setlName(results.getString("lName"));
			driObj.setPhoneNo(results.getString("phoneNo"));
			driObj.setSupplier(dbbp.retrieveSupplierById(results.getInt("supplierId")));
		}catch(Exception e){

		}
		return driObj;
	}
}
